1. Field of the Invention
The invention relates to a method, system, and program product for ordering, ranking, and retrieving of data stored in a database and to performing scalar operations on selected data rows stored in the database.
2. Description of Related Art
Numerous businesses process large numbers of transactions, including purchases of goods and services, repairs, maintenance management, inventory management, check clearing, bank account deposits and withdrawals, automatic teller machine transaction processing, credit card and debit card transaction processing, investment portfolio transaction processing (such as securities long sales and short sales, securities purchases, and margin account transactions), inventory management, and the like. The number of transactions per customer, the number of customers, the sequence of transactions, the frequency of transactions, and the magnitude of total transactions all can show subtle relationships, predict events, and conceal various anomalies, including fraud, inventory shrinkage, credit worthiness problems, identity theft, equipment failures, impending equipment failures, accident and failure “hot spots” and the like.
For example, a credit card company may create a risk score or a fraud score for a customer based on an analysis of that customer's usage of the credit card during a finite period, such as sixty or ninety days. Since different typical customers may have used their credit cards different numbers of times during the sixty or ninety days, the actual number of transactions for a specific customer will not be known in advance. It is frequently necessary to capture a customer's transaction history, for example, to derive a risk score, or to detect an anomalous set of transactions, for entry into an RDBMS, for subsequent access and use by other applications or processes.
Similarly, POS (Point of Sale) transaction analysis, either alone or in conjunction with customer loyalty and affinity programs allows a merchant to review market baskets, which vary between customers and for one customer over time, to analyze customer behavior.
Another area of transaction analysis is inventory management, including maintenance management and predictive maintenance. In this context transaction analysis of seemingly unrelated sub-unit failures or of seemingly unrelated repair orders and unrelated work orders, can be a predictor of larger future system failures.
Previously, these transaction analysis tasks have had a high overhead cost, especially in terms of processing, memory operations, and system bandwidth. There are several current approaches for this problem. One approach is for the data to be pre-processed by the RDBMS and then passed to an analytic application as an extracted flat row. Alternatively, an application can be written and used to retrieve specific data for each individual customer and pass the data to the analytic application for analysis. In each case, the score is captured into the RDBMS by again accessing the RDBMS.
The problem becomes especially severe with scalar aggregates. Relational database management systems provide a number of built in aggregate functions within the SQL language. These functions, such as MAX or COUNT, work across a group of rows within the RDBMS, where the rows are as defined, for example, in the GROUP BY clause. Many organizations have the need to execute custom scalar functions across multiple rows in the RDBMS, but do not have the mechanism, in terms of a particular scalar function or set of scalar functions, to perform the task.
Within this context, and given the very large numbers of transactions stored in the RDBMS and the many processes performed by the RDBMS, and the need to generate aggregates in order to analyze for anomalies and patterns (or elements of patterns) in any one transaction or row, or in a small set of rows or transactions, all within a large universe of transactions and rows, a clear need exists for a method, system, and program product to efficiently execute a scalar function on a variable number of rows (rows) within a RDBMS using the RDBMS' SQL, and to do so without storing intermediate rows (rows) in memory or to be pre-processed, especially in a large-scale partitioned RDBMS.
This is especially so for aggregates used to produce an individual customer score (as a risk assessment, buying pattern, customer loyalty, calling pattern, travel pattern, maintenance and/or spare parts pattern or failure or maintenance histories) result a customer or customers whose rows are in the RDBMS, and to produce the customer score quickly and simply with minimal system overhead.
As used herein “row” and “row” are used interchangeability, where “row” represents a “row” in a matrix representation of a database.
When a “customer score” is referred to herein, it is to be understood that the method, system, and program product described herein can be used to detect such profile items as buying patterns, calling patterns, travel patterns, spare parts and maintenance requirements, as well as anomalies, trends, and predictions in check processing, bank account deposit and withdrawal processing, automatic teller machine transaction processing, credit card and debit card transaction processing, investment portfolio transaction processing (such as securities long sales and short sales, securities purchases, and margin account transactions), inventory management, maintenance predictions, failure predictions, and the like, and unless the context indicates the contrary, such transactions are intended to be encompassed within the broad term of “customer score.”